package com.tangerine.bd;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import android.database.Cursor;
import com.tangerine.entidades.Poi;

public class DbHandlerPoi{
	//Handler
	private static DbHandler bd; 

	// Contacts table name
	private static final String TABLE_POI 		= "POI";
	private static final String TABLE_HISTORIAL = "HISTORIAL";
	private static final String TABLE_FAVORITO 	= "FAVORITO";

	// Campos tabla POI
	private static final int CAMPO_ID_POI 		= 0;
	private static final int CAMPO_NOMBRE		= 1;
	private static final int CAMPO_INFORMACION 	= 2;
	private static final int CAMPO_COORDENADA_X	= 3;
	private static final int CAMPO_COORDENADA_Y	= 4;

	//Constructor
	public DbHandlerPoi() {
		bd = DbHandler.getInstance();
	}
	
	public List<Poi> cargarListadoPoi() throws SQLException {
		List<Poi> list = new ArrayList<Poi>();

		String selectQuery = "SELECT * FROM " + TABLE_POI;
		
		Cursor cursor = bd.ejecutarConsulta(selectQuery); 
		
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Poi auxPoi = new Poi();
				auxPoi.setIdPoi(Integer.parseInt(cursor.getString(CAMPO_ID_POI)));
				auxPoi.setNombre(cursor.getString(CAMPO_NOMBRE));
				auxPoi.setInformacion(cursor.getString(CAMPO_INFORMACION));
				auxPoi.setCoordenadaX(Integer.parseInt(cursor.getString(CAMPO_COORDENADA_X)));
				auxPoi.setCoordenadaY(Integer.parseInt(cursor.getString(CAMPO_COORDENADA_Y)));
				list.add(auxPoi);
			} while (cursor.moveToNext());
		}
		
		cursor.close();

		return list;
	}
	
	public List<Poi> cargarListadoPoiHistorial() throws SQLException {
		List<Poi> list = new ArrayList<Poi>();

		String selectQuery = "SELECT DISTINCT P.* FROM " + TABLE_POI + " P, " + TABLE_HISTORIAL + " H WHERE P.ID_POI = H.ID_POI";
		
		Cursor cursor = bd.ejecutarConsulta(selectQuery); 
		
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Poi auxPoi = new Poi();
				auxPoi.setIdPoi(Integer.parseInt(cursor.getString(CAMPO_ID_POI)));
				auxPoi.setNombre(cursor.getString(CAMPO_NOMBRE));
				auxPoi.setInformacion(cursor.getString(CAMPO_INFORMACION));
				auxPoi.setCoordenadaX(Integer.parseInt(cursor.getString(CAMPO_COORDENADA_X)));
				auxPoi.setCoordenadaY(Integer.parseInt(cursor.getString(CAMPO_COORDENADA_Y)));
				list.add(auxPoi);
			} while (cursor.moveToNext());
		}
		
		cursor.close();
		
		return list;
	}
	
	public List<Poi> cargarListadoPoiFavorito() throws SQLException {
		List<Poi> list = new ArrayList<Poi>();

		String selectQuery = "SELECT DISTINCT P.* FROM " + TABLE_POI + " P, " + TABLE_FAVORITO + " F WHERE P.ID_POI = F.ID_POI";
		
		Cursor cursor = bd.ejecutarConsulta(selectQuery); 
		
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Poi auxPoi = new Poi();
				auxPoi.setIdPoi(Integer.parseInt(cursor.getString(CAMPO_ID_POI)));
				auxPoi.setNombre(cursor.getString(CAMPO_NOMBRE));
				auxPoi.setInformacion(cursor.getString(CAMPO_INFORMACION));
				auxPoi.setCoordenadaX(Integer.parseInt(cursor.getString(CAMPO_COORDENADA_X)));
				auxPoi.setCoordenadaY(Integer.parseInt(cursor.getString(CAMPO_COORDENADA_Y)));
				list.add(auxPoi);
			} while (cursor.moveToNext());
		}
		
		cursor.close();

		return list;
	}
	
	public void insertarPoi(Poi auxPoi) throws SQLException {
		String insertRegister = "INSERT INTO " + TABLE_POI + " (ID_POI, NOMBRE, INFORMACION, COORDENADA_X, COORDENADA_Y) VALUES(" +
								Integer.toString(auxPoi.getIdPoi()) + ", \"" + 		//ID_POI
								auxPoi.getNombre() + "\"," + 						//NOMBRE
								auxPoi.getInformacion() + "," + 					//INFORMACION
								Integer.toString(auxPoi.getCoordenadaX()) + "," +	//COORDENADA X
								Integer.toString(auxPoi.getCoordenadaY()) + ")";	//COORDENADA Y
		
		bd.ejecutarInsert(insertRegister);
	}

	//return 0 - TABLE POI empty.
	//return 1 - 
	public int existePoi() throws SQLException {
		int iCantReg = 0;

		String selectQuery = "SELECT COUNT(1) FROM " + TABLE_POI;
		
		Cursor cursor = bd.ejecutarConsulta(selectQuery); 

		if(cursor.moveToFirst()) {
			do {
				iCantReg = Integer.parseInt(cursor.getString(0));
			} while (cursor.moveToNext());
		}
		
		cursor.close();
		
		if(iCantReg == 0)
			return 0;
		else
			return 1;			
	}
	
	public int existePoiTabla(Poi auxPoi) throws SQLException {	
		int iCantReg = 0;
		
		String selectQuery = "SELECT COUNT(1) FROM " + TABLE_POI + " WHERE NOMBRE = \"" + auxPoi.getNombre() + "\"";
		
		Cursor cursor = bd.ejecutarConsulta(selectQuery); 

		if(cursor.moveToFirst()) {
			do {
				iCantReg = Integer.parseInt(cursor.getString(0));
			} while (cursor.moveToNext());
		}
		
		cursor.close();
		
		if(iCantReg == 0)
			return 0;
		else
			return 1;		
	}
}
	